﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data.Common;
using System.Collections;
using MyFrameWork.Common;
using System.Data;
using System.Reflection;
using MvcApplication5.Common;

namespace MyFrameWork.Dam
{
    #region 数据库操作

    public class DataDAM : BaseDAM
    {
        public DataDAM()
        {
            this.SetRejectList();
        }
        private ArrayList _rejectList;

        /// <summary>
        /// 新规数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="PKColumn">主键</param>
        /// <param name="targetList">字段及字段的值</param>
        /// <param name="dbManager"></param>
        /// <returns>受影响行数</returns>
        public int InsertData(string tableName, string PKColumn, Dictionary<string, object> targetList, DBManager dbManager)
        {
            try
            {
                if (_rejectList == null)
                {
                    _rejectList = new ArrayList();
                }

                StringBuilder targetString = new StringBuilder();
                string keys = string.Empty;
                string values = string.Empty;

                foreach (string key in targetList.Keys)
                {
                    if (("," + PKColumn + ",").IndexOf("," + key + ",") < 0 && !_rejectList.Contains(key))
                    {
                        keys += string.Format("{0}, ", key);
                        values += string.Format("@{0}, ", key);
                    }
                }

                string sql = string.Format(@"
                    INSERT INTO
                        {0}
                        ({1}, create_user, create_date, update_user, update_date)
                    VALUES
                        ({2}, @create_user, @create_date, @update_user, @update_date)", tableName, CommonUtility.StripEndingString(keys, ","), CommonUtility.StripEndingString(values, ","));

                DbCommand command = GetSqlStringCommand(sql);

                foreach (string key in targetList.Keys)
                {
                    if (_rejectList.Contains(key))
                    {
                        continue;
                    }

                    string targetType = targetList[key].GetType().Name;
                    switch (targetType)
                    {
                        case "Decimal":
                            AddInParameter(command, "@" + key, DbType.Decimal, targetList[key] == DBNull.Value ? 0 : Convert.ToDecimal(targetList[key]));
                            break;
                        case "Int32":
                            AddInParameter(command, "@" + key, DbType.Int32, targetList[key] == DBNull.Value ? 0 : Convert.ToInt32(targetList[key]));
                            break;
                        case "DateTime":
                            if (Convert.ToDateTime(targetList[key]) == CmnConsts.NULL_DATE)
                            {
                                AddInParameter(command, "@" + key, DbType.DateTime, null);
                            }
                            else
                            {
                                AddInParameter(command, "@" + key, DbType.DateTime, Convert.ToDateTime(targetList[key]));
                            }
                            break;
                        default:
                            AddInParameter(command, "@" + key, DbType.String, string.IsNullOrEmpty(targetList[key].ToString()) ? "" : targetList[key].ToString());
                            break;
                    }
                }

                AddInParameter(command, "@create_user", DbType.String, AppContext.Current.LoginUser.employee_id);
                AddInParameter(command, "@create_date", DbType.DateTime, DateTime.Now);
                AddInParameter(command, "@update_user", DbType.String, AppContext.Current.LoginUser.employee_id);
                AddInParameter(command, "@update_date", DbType.DateTime, DateTime.Now);

                return ExecuteNonQuery(command, dbManager);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 新规数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="PKColumn">主键</param>
        /// <param name="model">要新规的model</param>
        /// <param name="dbManager"></param>
        /// <returns>受影响行数</returns>
        public int InsertData(string tableName, string PKColumn, object model, DBManager dbManager)
        {
            FieldInfo[] fieldList = model.GetType().GetFields();

            Dictionary<string, object> targetList = new Dictionary<string, object>();

            foreach (FieldInfo info in fieldList)
            {
                if (info.FieldType.Name == "String" && info.GetValue(model) == null)
                {
                    targetList.Add(info.Name, "");
                }
                else
                {
                    targetList.Add(info.Name, info.GetValue(model));
                }
            }

            return this.InsertData(tableName, PKColumn, targetList, dbManager);
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="targetList">要修改的字段及字段的值</param>
        /// <param name="conditionList">条件</param>
        /// <param name="dbManager"></param>
        /// <returns>受影响行数</returns>
        public int UpdateData(string tableName, Dictionary<string, string> targetList, ConditionList conditionList, DBManager dbManager)
        {
            try
            {
                StringBuilder targetString = new StringBuilder();
                String condition = string.Empty;

                foreach (string key in targetList.Keys)
                {
                    targetString.AppendFormat(" {0} = '{1}', ", key, targetList[key]).AppendLine();
                }

                if (conditionList.Count > 0)
                {
                    condition = string.Format(" WHERE {0} ", conditionList.ToString());
                }

                string sql = string.Format(@"
                    UPDATE 
                        {0}
                    SET
                        {1}, update_user=@update_user, update_date=@update_date
                        {2}", tableName, CommonUtility.StripEndingString(targetString.ToString(), ","), condition);

                DbCommand command = GetSqlStringCommand(sql);

                AddInParameter(command, "@update_user", DbType.String, AppContext.Current.LoginUser.employee_id);
                AddInParameter(command, "@update_date", DbType.DateTime, DateTime.Now);

                return ExecuteNonQuery(command, dbManager);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="model">修改字段以model传入</param>
        /// <param name="conditionList">条件</param>
        /// <param name="dbManager"></param>
        /// <returns>受影响行数</returns>
        public int UpdateData(string tableName, object model, ConditionList conditionList, DBManager dbManager)
        {
            FieldInfo[] fieldList = model.GetType().GetFields();

            Dictionary<string, object> targetList = new Dictionary<string, object>();

            foreach (FieldInfo info in fieldList)
            {
                targetList.Add(info.Name, info.GetValue(model));
            }

            return this.UpdateData(tableName, targetList, conditionList, dbManager);
        }


        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="conditionList">条件</param>
        /// <param name="dbManager"></param>
        /// <returns>受影响行数</returns>
        public int DeleteData(string tableName, ConditionList conditionList, DBManager dbManager)
        {
            try
            {
                String condition = string.Empty;

                if (conditionList.Count > 0)
                {
                    condition = string.Format(" WHERE {0} ", conditionList.ToString());
                }

                string sql = string.Format(@"
                    DELETE FROM 
                        {0}
                        {1}", tableName, condition);

                DbCommand command = GetSqlStringCommand(sql);

                return ExecuteNonQuery(command, dbManager);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="field">查询字段</param>
        /// <param name="conditionList">条件</param>
        /// <param name="dbManager"></param>
        /// <returns>DataSet</returns>
        public DataSet SelectData(string tableName, string field, ConditionList conditionList, DBManager dbManager)
        {
            String condition = string.Empty;

            if (conditionList.Count > 0)
            {
                condition = string.Format(" WHERE {0} ", conditionList.ToString());
            }

            string sql = string.Format(@"
                SELECT 
                    {0}
                FROM
                    {1}
                    {2}", field, tableName, condition);

            DbCommand command = GetSqlStringCommand(sql);

            return ExecuteDataSet(command, dbManager);
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="field">查询字段</param>
        /// <param name="dbManager"></param>
        /// <returns>DataSet</returns>
        public DataSet SelectData(string tableName, string field, DBManager dbManager)
        {
            return this.SelectData(tableName, field, new ConditionList(ConditionList.ConditionType.AND), null);
        }

        /// <summary>
        /// 过滤字段（不想插入的字段）
        /// </summary>
        /// <param name="rejectColumnList"></param>
        public void SetRejectList()
        {
            _rejectList = new ArrayList();

            _rejectList.Add("create_user");
            _rejectList.Add("create_date");
            _rejectList.Add("update_user");
            _rejectList.Add("update_date");
        }
    }
    #endregion


    #region Sql语句条件的处理

    public class ConditionList
    {
        public enum ConditionType
        {
            AND = 0,
            OR = 1
        }

        public enum EquipType
        {
            EQUIP,
            LESS,
            MORE,
            LESS_EQUIP,
            MORE_EQUIP,
            LIKE,
            IN,
            NOT_EQUIP
        }

        private ArrayList _conditionList;
        private ConditionType _conditionType;

        public int Count
        {
            get { return _conditionList == null ? 0 : _conditionList.Count; }
        }

        public ConditionList(ConditionType conditionType)
        {
            _conditionList = new ArrayList();
            _conditionType = conditionType;
        }

        public void Add(string key, EquipType equipType, string value)
        {
            switch (equipType)
            {
                case EquipType.EQUIP:
                    _conditionList.Add(string.Format("{0} {1} '{2}'", key, "=", value));
                    break;

                case EquipType.LESS:
                    _conditionList.Add(string.Format("{0} {1} '{2}'", key, "<", value));
                    break;

                case EquipType.MORE:
                    _conditionList.Add(string.Format("{0} {1} '{2}'", key, ">", value));
                    break;

                case EquipType.LESS_EQUIP:
                    _conditionList.Add(string.Format("{0} {1} '{2}'", key, "<=", value));
                    break;

                case EquipType.MORE_EQUIP:
                    _conditionList.Add(string.Format("{0} {1} '{2}'", key, ">=", value));
                    break;

                case EquipType.LIKE:
                    _conditionList.Add(string.Format("{0} {1} '{2}'", key, "like", value));
                    break;

                case EquipType.IN:
                    _conditionList.Add(string.Format("{0} {1} ({2})", key, "in", value));
                    break;

                case EquipType.NOT_EQUIP:
                    _conditionList.Add(string.Format("{0} {1} '{2}'", key, "<>", value));
                    break;
            }
        }

        public void Add(ConditionList conditionList)
        {
            _conditionList.Add(conditionList.ToString());
        }

        public override string ToString()
        {
            string resultString = string.Empty;

            if (_conditionList.Count == 0) { return string.Empty; }

            foreach (string condition in _conditionList)
            {
                switch (_conditionType)
                {
                    case ConditionType.AND:
                        resultString += string.Format(" {0} AND ", condition);
                        break;

                    case ConditionType.OR:
                        resultString += string.Format(" {0} OR ", condition);
                        break;
                }
            }
            if (_conditionType == ConditionType.AND)
            {
                resultString = string.Format("({0})", CommonUtility.StripEndingString(resultString, "AND"));
            }
            else
            {
                resultString = string.Format("({0})", CommonUtility.StripEndingString(resultString, "OR"));

            }

            return resultString;
        }
    }
    #endregion


    #region model转换与赋值

    public class DataConvert<T> where T : new()
    {
        public static List<T> ConvertModelToDataSet(DataSet ds)
        {
            T t = new T();

            if (ds.Tables.Count == 0)
            {
                return new List<T>();
            }

            List<T> modelList = new List<T>();

            FieldInfo[] fieldList = t.GetType().GetFields();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                T model = new T();

                foreach (FieldInfo fieldInfo in fieldList)
                {
                    if (dr.Table.Columns.Contains(fieldInfo.Name))
                    {
                        fieldInfo.SetValue(model, CommonUtility.GetNotNullData(dr[fieldInfo.Name], fieldInfo.FieldType.Name));
                    }
                }

                modelList.Add(model);
            }

            return modelList;
        }

        public static Dictionary<string, object> ConvertModelToDictory(T model)
        {
            FieldInfo[] fieldList = model.GetType().GetFields();

            Dictionary<string, object> targetList = new Dictionary<string, object>();

            foreach (FieldInfo info in fieldList)
            {
                if (info.FieldType.Name == "String" && info.GetValue(model) == null)
                {
                    targetList.Add(info.Name, "");
                }
                else
                {
                    targetList.Add(info.Name, info.GetValue(model));
                }
            }

            return targetList;
        }
    }

    #endregion

}